#!/usr/bin/python
# -*- coding: utf-8 -*-  
"""
@Project : hello 
@file : genert_sql.py
@Author : shenj
@time : 2025/4/22 15:17
@func :
"""

import requests
from datetime import datetime
import time
import logging
from com.cn.for_cdc.common.log_helper import configure_logging
configure_logging()

def generate_data_consistency_check(coloum_name,source_table,target_table):
    coloum_name = coloum_name.strip().lower()
    source_table = source_table.strip().upper()
    target_table = target_table.strip().upper()
    sql=f"""
检查{coloum_name}字段的数据一致性：
{source_table}表中存在该字段值，但{target_table}表中无对应记录。
    """
    logging.info(sql)


# def generate_check_sql(coloum_name,source_table,target_table):
#
#     pass

def generate_check_sql(
        bu_id_value: str,
        parallel_degree: int = 8,
        main_schema: str = "SIEBEL",
        main_table: str = "S_CONTACT",
        db_link: str = "SBL",
        main_alias: str = "sibl",
        main_bu_id_col: str = "BU_ID",
        main_row_id_col: str = "row_id",
        stg_schema: str = "",
        stg_table: str = "stg_b_contact",
        stg_contact_id_col: str = "contact_id",
        stg_bu_id_col: str = "BU_ID",
        stg_alias: str = "stg"
) -> str:
    """生成带NOT EXISTS条件的优化查询SQL

    :param db_link:
    :param bu_id_value: BU_ID的过滤值
    :param parallel_degree: 并行查询提示值，默认8
    :param main_schema: 主表模式名，默认SIEBEL
    :param main_table: 主表名，默认S_CONTACT
    :param main_alias: 主表别名，默认sbl
    :param main_bu_id_col: 主表BU_ID列名，默认BU_ID
    :param main_row_id_col: 主表ROW_ID列名，默认row_id
    :param stg_schema: 目标表模式名，默认空
    :param stg_table: 目标表名，默认stg_b_contact
    :param stg_contact_id_col: 目标表联系ID列名，默认contact_id
    :param stg_bu_id_col: 目标表BU_ID列名，默认BU_ID
    :param stg_alias: 目标表别名，默认stg
    :return: 生成的SQL语句
    """

    # 构建模式前缀
    main_schema_prefix = f"{main_schema}." if main_schema else ""
    stg_schema_prefix = f"{stg_schema}." if stg_schema else ""

    # 构造主查询部分
    sql = f"SELECT /*+ parallel({parallel_degree}) */ {main_alias}.{main_row_id_col} \n"
    sql += f"FROM {main_schema_prefix}{main_table}@{db_link} {main_alias}\n"
    sql += f"WHERE {main_alias}.{main_bu_id_col} = '{bu_id_value}' "
    sql += f"AND {main_alias}.{main_row_id_col} IS NOT NULL\n"
    sql += "  AND NOT EXISTS (\n"
    sql += f"    SELECT 1 \n"
    sql += f"    FROM {stg_schema_prefix}{stg_table} {stg_alias}\n"
    sql += f"    WHERE {stg_alias}.{stg_contact_id_col} = {main_alias}.{main_row_id_col} "
    sql += f"AND {stg_alias}.{stg_bu_id_col} = '{bu_id_value}'\n"
    sql += "  );"
    logging.info(sql)
    return sql

if __name__ == '__main__':
    coloum_name='  CTLG_CAT_PROD_ID             '
    source_table=' SIEBEL.S_CTLG_CAT_PROD   '
    target_table='     STG_B_CTLG_CAT_PROD             '

    generate_data_consistency_check(coloum_name,source_table,target_table)
    # generate_check_sql(coloum_name,source_table,target_table)

'''
SELECT /*+ parallel(8)  */ ROW_ID FROM SIEBEL.S_CTLG_CAT_PROD@SBL s WHERE PROD_ID IN (
SELECT ROW_ID FROM SIEBEL.S_PROD_INT@SBL WHERE BU_ID = '1-7V7X'
)
MINUS
SELECT /*+ parallel(8)  */ CTLG_CAT_PROD_ID FROM STG_B_CTLG_CAT_PROD WHERE BU_CODE = 'ICIBE' ;

'''